# -*- coding: utf-8 -*-
import sqlite3 as sqlite
from setting import DB_NAME
import log

# log.info('创建数据库:%s' % DB_NAME)
# 建立与数据库的连接，如果不存在，则新建
sql_db = sqlite.connect(DB_NAME)

# 游标
cu = sql_db.cursor()
tb_src_tree = 'tb_source_tree'
tb_src = 'tb_source'
tb_src_page = 'tb_source_page'

def add_table():
    
    log.info(u'创建数据库表:%s(%s)' % (u'树形层级菜单', tb_src_tree))
    is_exists = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='%s'" % (tb_src_tree)
    cu.execute(is_exists)
    res = cu.fetchall()
    # 不存在tb_source_tree表创建
    if(res[0][0] == 0):
        '''
        创建数据库表
        '''
        '''创建树形层级菜单表
            id:菜单id
            pid:父菜单id
            name:菜单名
            pname:父菜单名
            isparent:是否是父菜单,0=是,1=否
            isread:是否已读,0=是,1=否'''
            
        cu.execute('''create table %s
                        ( id varchar(20), pid varchar(20), name varchar(35), t varchar(100), pname varchar(100), isparent integer, isread integer)
                    ''' % tb_src_tree)
        log.info(u'创建成功!')
    else:
        pass
        log.info(u'%s表已存在!' % tb_src_tree)
        
    log.info(u'创建数据库表:%s(%s)' % (u'资源信息', tb_src))
    is_exists = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='%s'" % (tb_src)
    cu.execute(is_exists)
    res = cu.fetchall()
    
    # 不存在tb_source表创建
    if(res[0][0] == 0):
        '''创建资源信息表
            treepath:当前资源所属树形菜单目录层级
            srcname:资源名称
            srctype:资源文件类型
            downloadurl:资源下载链接
            isdownload:资源是否已下载,0=是,1=否'''
        
        cu.execute('''create table %s
                        (treepath varchar(100), srcname varchar(300), srctype varchar(10), downloadurl varchar(400), isdownload integer)
                    ''' % tb_src)
        log.info(u'创建成功!')
    else:
        pass
        log.info(u'%s表已存在!' % tb_src)
        
    
    log.info(u'创建数据库表:%s(%s)' % (u'资源分页记录', tb_src_page))
    is_exists = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='%s'" % (tb_src_page)
    cu.execute(is_exists)
    res = cu.fetchall()
    
    # 不存在tb_source表创建
    if(res[0][0] == 0):
        '''创建资源信息表
            id:菜单id
            pagecount:总页数
            currentpage:当前页'''
        
        cu.execute('''create table %s
                        (id varchar(100), pagecount integer,  currentpage integer)
                    ''' % tb_src_page)
        log.info(u'创建成功!')
    else:
        pass
        log.info(u'%s表已存在!' % tb_src_page)
        
def add_tree_data(pkid, pid, name, t, pname, isparent):
    '''
        添加树形菜单数据到表tb_source_tree
    '''
    log.info(u'保存树形菜单数据[%s,%s,%s,%s,%s,%d]' % (pkid, pid, name, t, pname, isparent))
    is_exists_sql = "select count(id) from %s where id='%s'" % (tb_src_tree, pkid)
    cu.execute(is_exists_sql)
    if (cu.fetchall()[0][0] > 0):
        log.warn(u'已存在记录:%s' % pkid)
        return
    
    add_tree_sql = "insert into %s values('%s','%s','%s','%s','%s',%s,%s)" % (tb_src_tree, pkid, pid, name.replace('\'', '\'\''), 
                                                                              t.replace('\'', '\'\''), pname.replace('\'', '\'\''), isparent, 1)
    cu.execute(add_tree_sql)
    sql_db.commit()
    log.info(u'成功!')

def update_tree_read(pkid):
    '''
        标识当前资源已经被读取
    '''
    sql = "update %s set isread=0 where id='%s'" % (tb_src_tree, pkid)
    log.info('标识当前资源菜单id已经被读取id:%s' % pkid)
    cu.execute(sql)
    sql_db.commit()
    log.info(u'成功!')


def find_child_tree_isread():
    '''
        查询所有未读的子节点
    '''
    log.info('读取所有未被读取的子节点')
    query_sql = 'select * from %s where isparent=1 and isread=1' % tb_src_tree
    return cu.execute(query_sql)


def add_update_page_data(pkid, pagecount, currentpage):
    '''
        添加或更新当前资源的分页数
    '''
    is_exists_sql = "select count(id) from %s where id='%s'" % (tb_src_page, pkid)
    cu.execute(is_exists_sql)
    if (cu.fetchall()[0][0] > 0):
        log.warn(u'已存在记录:%s' % id)
        log.info(u'更新当前处理的分页信息')
        sql = "update %s set currentpage=%s WHERE id='%s'" % (tb_src_page, currentpage, pkid)
        cu.execute(sql)
        sql_db.commit()
        log.info(u'成功！')
    else:
        log.info(u'保存当前资源的分页信息[%s,%d,%d]' % (pkid, pagecount, currentpage))
        sql = "insert into %s values('%s', %s, %s)" % (tb_src_page, pkid, pagecount, currentpage)
        cu.execute(sql)
        sql_db.commit()
        log.info(u'成功！')
        
def query_tree():
    query_sql = 'select id, name from %s' % tb_src_tree
    cu.execute(query_sql)
    for data in cu.fetchall():
        log.info(data[1])
        log.info(data[1])
    
if __name__ == '__main__':
#     add_table()
    query_tree()
